package it.orma.iridehr.repository.ricercaOpportunita;

import it.orma.iridehr.controllers.form.ricercaOpportunita.RicercaOpportunitaForm;
import it.orma.iridehr.entities.*;
import it.orma.iridehr.entities.azienda.Azienda;
import it.orma.iridehr.entities.azienda.ContattoAzienda;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

@Repository
public class RicercaOpportunitaRepository {

	@PersistenceContext
	private EntityManager entityManager;

	public Opportunita caricaOpportunita(Long idOpportunita){
		String hql = "select s from "+Opportunita.class.getName()+" as s where s.idOpportunita = ?";
		Opportunita opp = (Opportunita) entityManager.createQuery(hql).setParameter(1, idOpportunita).getSingleResult();
		entityManager.detach(opp);
		return opp;
	}
	
	public Opportunita caricaOpportunitaByProgr(Long progr){
		String hql = "select s from "+Opportunita.class.getName()+" as s where s.progressivo = ?";
		Opportunita opp = (Opportunita) entityManager.createQuery(hql).setParameter(1, progr).getSingleResult();
		entityManager.detach(opp);
		return opp;
	}
	
	public Opportunita creaOpportunita(){
		Opportunita opp = new Opportunita();
		opp.setDataCreazione(new Date());
		Azienda az = new Azienda();
		opp.setAzienda(az);
		return opp;
	}	
	
	public Integer countOpportunita(RicercaOpportunitaForm opForm){
		
		String hql = "select count(*) from "+Opportunita.class.getCanonicalName()+" as n where 1=1 ";
		if (opForm.getCodice()!=null&&!opForm.getCodice().equals("ALL"))
			hql+=" AND n.codice = ? ";
	    if (opForm.getProgressivo()!=null&&!opForm.getProgressivo().trim().equals(""))
	    	  hql+=" AND n.progressivo = ? ";
	    if (opForm.getDataDa()!=null&&!opForm.getDataDa().equals(""))
	    	  hql+=" AND n.dataCreazione >= STR_TO_DATE(?, '%d/%m/%Y') ";
	    if (opForm.getDataA()!=null&&!opForm.getDataA().equals(""))
	    	  hql+=" AND n.dataCreazione <= STR_TO_DATE(?, '%d/%m/%Y') ";
	     
		Query query = entityManager.createQuery(hql);
		int i=0;
		if (opForm.getCodice()!=null&&!opForm.getCodice().equals("ALL"))
			query.setParameter(++i, opForm.getCodice());
	      if (opForm.getProgressivo()!=null&&!opForm.getProgressivo().trim().equals(""))
	    	  query.setParameter(++i, Long.parseLong(opForm.getProgressivo()));
	      if (opForm.getDataDa()!=null&&!opForm.getDataDa().equals(""))
	    	  query.setParameter(++i, opForm.getDataDa());
	      if (opForm.getDataA()!=null&&!opForm.getDataA().equals(""))
	    	  query.setParameter(++i, opForm.getDataA());
	      
		return (new Long((Long) query.getSingleResult()).intValue());
	}
	
	public List<Opportunita> caricaOpportunita(int page,RicercaOpportunitaForm opForm){
			
			String nativeSql = "SELECT *"+
					  " FROM (SELECT op.id_opportunita,"+
				      "         op.progressivo,"+
				      "         op.codice," +
				      "			op.descrizione," +
				      "			az.id_azienda," +
				      "			az.nome "+
				      " FROM opportunita op, azienda az"+                 
				      "   WHERE op.id_azienda = az.id_azienda ";
				      if (opForm.getCodice()!=null&&!opForm.getCodice().equals("ALL"))
				    	  nativeSql+=" AND op.codice = ? ";
				      if (opForm.getProgressivo()!=null&&!opForm.getProgressivo().trim().equals(""))
				    	  nativeSql+=" AND op.progressivo = ? ";
				      if (opForm.getDataDa()!=null&&!opForm.getDataDa().equals(""))
				    	  nativeSql+=" AND op.dt_creazione >= STR_TO_DATE(?, '%d/%m/%Y') ";
				      if (opForm.getDataA()!=null&&!opForm.getDataA().equals(""))
				    	  nativeSql+=" AND op.dt_creazione <= STR_TO_DATE(?, '%d/%m/%Y') ";
				      nativeSql+="  ORDER BY op.progressivo ASC) view "+
				      " LIMIT "+(page-1)*2+", 10";
					
			List<Opportunita> list = new ArrayList<Opportunita>();
			Query query = entityManager.createNativeQuery(nativeSql);
			int i=0;
			if (opForm.getCodice()!=null&&!opForm.getCodice().equals("ALL"))
				query.setParameter(++i, opForm.getCodice());
		      if (opForm.getProgressivo()!=null&&!opForm.getProgressivo().trim().equals(""))
		    	  query.setParameter(++i, Long.parseLong(opForm.getProgressivo()));
		      if (opForm.getDataDa()!=null&&!opForm.getDataDa().equals(""))
		    	  query.setParameter(++i, opForm.getDataDa());
		      if (opForm.getDataA()!=null&&!opForm.getDataA().equals(""))
		    	  query.setParameter(++i, opForm.getDataA());
			
			List<Object[]> result = (List<Object[]> )query.getResultList();
			Iterator<Object[]> resultIt = result.iterator();
			while(resultIt.hasNext()){
				Object[] record = resultIt.next();
				Opportunita opp = new Opportunita();
				opp.setIdOpportunita(((BigInteger)record[0]).longValue());
				opp.setProgressivo(((BigInteger)record[1]).longValue());
				opp.setCodice((String)record[2]);
				opp.setDescrizione((String)record[3]);
				Azienda az = new Azienda();
				az.setIdAzienda((Integer)record[4]);
				az.setNome((String) record[5]);
				opp.setAzienda(az);
				list.add(opp);
			}
			
			entityManager.detach(result);
			entityManager.detach(list);
			
			return list;
		
	}

	public List<ContattoAzienda> caricaCandidati(Long idOpportunita) {
		// TODO Auto-generated method stub
		return null;
	}
	
}
